package dejava.bolsadetrabajo.repository.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



import dejava.bolsadetrabajo.repository.AnuncianteRepository;
import dejava.bolsadetrabajo.repository.model.Curriculum;
import dejava.bolsadetrabajo.repository.model.Postulante;

public class AnuncianteRepositoryJdbc implements AnuncianteRepository {

	//@Override
	public List<Curriculum> obtenerProfesionales(String especialidad,
			String industria, String experiencia, String ubicacion) throws SQLException {
		
		List<Curriculum> listcu = new ArrayList<Curriculum>();
		
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		

		  String sql = "";
		  sql += "{ call sp_ObtenerProfesionalesPorCriterios(?,?,?,?) }";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bolsatrabajo",
			        "root","123");

			   CallableStatement prcProcedimientoAlmacenado = conn.prepareCall(sql);
			   prcProcedimientoAlmacenado.setString(1, especialidad);
			   prcProcedimientoAlmacenado.setString(2, industria);
			   prcProcedimientoAlmacenado.setString(3, experiencia);
			   prcProcedimientoAlmacenado.setString(4, ubicacion);
			

			   rs = prcProcedimientoAlmacenado.executeQuery();

			
		
			while (rs.next()){
			Curriculum c =new Curriculum();
			
				c.setIdCurriculum(rs.getInt("idCurriculum"));
				c.setIdPostulante(rs.getInt("idPostulante"));
				c.setFoto(rs.getString("foto"));
				c.setCv(rs.getString("cv"));
			
			listcu.add(c);
				
				
				
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return listcu;		
	}

	//@Override
	public Postulante obtenerDatosPostulante(int idpos) throws SQLException {
		
	

		Postulante p = new Postulante();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		 String sql = "";
			sql += "{ call sp_ObtenerDatosPostulante(?) }";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bolsatrabajo",
					"root", "123");

			ps = conn.prepareStatement(sql);
			ps.setInt(1,idpos);
			rs = ps.executeQuery();
			
			  CallableStatement prc = conn.prepareCall(sql);
			   prc.setInt(1,idpos);
			   rs = prc.executeQuery();

			while (rs.next()) {

				p.setNombre(rs.getString("nombre"));
				p.setCorreo(rs.getString("correo"));
				p.setTitulo(rs.getString("titulo"));
				

			}
		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " + except.getMessage());
			throw ex;
		} finally {
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}
		return p;
	}

}
